package com.winway.sixbook.service;

import java.text.SimpleDateFormat;
import java.util.Date;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataBaseManagementHelper extends SQLiteOpenHelper{
	
	private static final String TAG = "UserDataManager";
	private static DataBaseManagementHelper instance = null;
	
	private static final String DB_NAME = "sixbook";
	private static final String TABLE_NAME = "users";
	public static final String  ID = "_id";

	private static final int DB_VERSION = 10;

	public static final String USER_NAME = "user_name";
	public static final String USER_PWD = "user_pwd";
	public static final String SILENT = "silent";
	public static final String VIBRATE = "vibrate";
	
	private static final String TABLE_NAME1 = "gggrecord";
	public static final String USER_ID = "user_id";
	public static final String CONTENT_ZM = "content_zm";
	public static final String TYPE_ID = "type_id";
	public static final String TYPE_NAME = "type_name";
	public static final String ADD_TIME = "add_time";
	public static final String TIME_SLOT = "time_slot";
	public static final String ADD_HOUR = "add_hour";
	public static final String ADD_MIN = "add_min";
	public static final String CONTENT_FM = "content_fm";
	public static final String CONTENT_WY = "content_wy";
	public static final String ISQY = "isqy";
	public static final String ADD_SEC = "add_sec";
	public static final String ISDO = "isdo";
	
	private static final String TABLE_NAME3 = "seeds";
	public static final String SEED_NAME = "seed_name";
	
	private static final String DB_CREATE = "CREATE TABLE " + TABLE_NAME + " ("
			+ ID + " integer primary key," + USER_NAME + " varchar,"
			+ USER_PWD + " varchar );";
	
	private static final String DB_CREATE1 = "CREATE TABLE " + TABLE_NAME1 + " ("
			+ ID + " integer primary key," 
			+ USER_ID + " varchar,"
			+ CONTENT_ZM + " text,"
			+ TYPE_ID + " integer,"
			+ TYPE_NAME + " varchar,"
			+ ADD_TIME + " varchar,"
			+ TIME_SLOT + " integer,"
			+ ADD_HOUR + " integer,"
			+ ADD_MIN + " integer,"
			+ CONTENT_FM + " text,"
			+ CONTENT_WY + " text,"
			+ ISQY + " integer ,"
			+ ADD_SEC +" integer,"
			+ ISDO +" integer );";
	
	private static final String DB_CRATE3 = "CREATE TABLE  counttable "
			+ "(_id integer primary key, " +
			"typeid integer, " +
			"countnum integer, " +
			"countname varchar,  " +
			"add_time varchar,  " +
			"counttoday integer," +
			"targetnum integer," +
			"begindate varchar," +
			"enddate varchar ); ";
	
	private static final String DB_CREATE3 = "CREATE TABLE " + TABLE_NAME3 + " ("
			+ ID + " integer primary key," 
			+ SEED_NAME + " varchar);";
	
	private DataBaseManagementHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}
	
	/** 采用双重检查加锁实例化单件*/
	public static DataBaseManagementHelper getInstance(Context context) {
		if(instance == null) {   //第一次检查
			synchronized (DataBaseManagementHelper.class){
				if(instance == null) //第二次检查
				{
					instance = new DataBaseManagementHelper(context);
				}
			}
		}
		return instance;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.i(TAG,"db.getVersion()="+db.getVersion());
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME + ";");
		db.execSQL(DB_CREATE);
		Log.i(TAG, "db.execSQL(DB_CREATE)");
		Log.e(TAG, DB_CREATE);
		
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME1 + ";");
		db.execSQL(DB_CREATE1);
		Log.i(TAG, "db.execSQL(DB_CREATE1)");
		Log.e(TAG, DB_CREATE1);

		
		db.execSQL("DROP TABLE IF EXISTS counttable ;");
		db.execSQL(DB_CRATE3);
		Log.i(TAG, "db.execSQL(DB_CREATE3)");
		Log.e(TAG, DB_CREATE1);

		
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME3 + ";");
		db.execSQL(DB_CREATE3);
		Log.i(TAG, "db.execSQL(DB_CREATE3)");
		Log.e(TAG, DB_CREATE3);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		for (int j = oldVersion; j <= newVersion; j++) {
            switch (j) {
            	case 10:
					Log.i(TAG, "DataBaseManagementHelper onUpgrade");
					SimpleDateFormat dateformat1=new SimpleDateFormat("yyyy-MM-dd");
					db.beginTransaction();
					//数据库升级步骤：1、将表名改为临时表；2、创建新表；3、导入数据；4、删除临时表
					db.execSQL("ALTER TABLE gggrecord RENAME TO gggrecord_bak");
					db.execSQL(DB_CREATE1);
					System.out.println("oldVersion:"+oldVersion);
					
					if(oldVersion==6){
						db.execSQL("INSERT INTO gggrecord SELECT *, '1','0' FROM gggrecord_bak;");
					}else if(oldVersion==9){
						db.execSQL("INSERT INTO gggrecord SELECT *, '0' FROM gggrecord_bak;");
					}else{
						db.execSQL("INSERT INTO gggrecord SELECT *, '0' FROM gggrecord_bak;");
					}
					db.execSQL("update gggrecord set add_sec=1 where add_sec isnull;");
					
					db.execSQL("ALTER TABLE counttable RENAME TO counttable_bak");
					db.execSQL(DB_CRATE3);
					if(oldVersion==6){
						db.execSQL("INSERT INTO counttable SELECT *,'"+dateformat1.format(new Date())+"','0','0','"+dateformat1.format(new Date())+"','"+dateformat1.format(new Date())+"' FROM counttable_bak;");
					}else if(oldVersion==8){
						db.execSQL("INSERT INTO counttable SELECT *,'0','"+dateformat1.format(new Date())+"','"+dateformat1.format(new Date())+"' FROM counttable_bak;");
					}else{
						db.execSQL("INSERT INTO counttable SELECT * FROM counttable_bak;");
					}
					
					db.execSQL("ALTER TABLE seeds RENAME TO seeds_bak");
					db.execSQL(DB_CREATE3);
					db.execSQL("INSERT INTO seeds SELECT * FROM seeds_bak;");
					
					//将临时表删除掉
					db.execSQL("DROP TABLE IF EXISTS gggrecord_bak ;");
					db.execSQL("DROP TABLE IF EXISTS counttable_bak ;");
					db.execSQL("DROP TABLE IF EXISTS seeds_bak ;");
					
					db.setTransactionSuccessful();
					db.endTransaction();
					
	                break;
	            default:
	                break;
	            }
	        }
	}

}
